# -*- coding: utf-8 -*-
"""
Created on Tue Sep 29 17:29:30 2020
@author: juliang
"""
import pymysql
import time
from datetime import datetime
from WindPy import *
w.start()

'''
连接数据库
'''
class pymysqlops:
    '''
    数据库基本信息
    '''
    def __init__(self,host,port,user,password,database):
        self.host = host
        self.database = database
        self.user = user
        self.password = password
        self.port = port
        
    '''
    连接数据库
    '''
    def db_connection(self):
        return pymysql.connect(host=self.host,database=self.database,user=self.user,
                               password=self.password,port=self.port)

    '''
    通过sql语句筛选,返回数据
    '''
    def select_table_by_sql(self,sql):
        try:
            conn = self.db_connection()
            cur = conn.cursor()
            cur.execute(sql)
            data = cur.fetchall()
            cur.close()
            conn.close()
            return data
        except Exception as e:
            data = ("error with sql",sql,e)
            return data
    '''
    执行更新数据库的功能（更新、插入、删除等）
    '''
    def Execute_sql(self,sql):
        conn = self.db_connection()
        cur = conn.cursor()
        try:
            cur.execute(sql)
            conn.commit()
        except :
            conn.rollback()
            print("execute sql with error")
        finally:
            cur.close()
            conn.close()
    '''
    多行同时插入
    '''   
    def insert_many_table(self,sql,valuelist):
        conn = self.db_connection()
        cur = conn.cursor()
        try:
            cur.executemany(sql,valuelist)
            conn.commit()
        except Exception as e:
            conn.rollback()
            print("insert with error",e)
        finally:
            cur.close()
            conn.close()


class data_in:
    '''
    更新数据到本地
    '''
    def __init__(self):
        self.desc = "数据更新到本地"

    def refresh_stock_mkt(self,start_date,end_date):
        '''
        start_date = "2016-03-09"
        end_date = "2020-09-29"
    
        1 深圳交易所所有交易日（上海1990-12-19开始，深圳1990-12-03开始）
          w.tdays("1990-01-01", "2020-09-30", "TradingCalendar=SZSE")
        2 每个交易日所有的股票（每天所有的股票代码）
          w.wset("sectorconstituent","date=1990-12-29;sectorid=a001010100000000;field=wind_code,sec_name")
        3 所有股票的当天既定指标
          w.wss
        4 这样做的原因：
          首先：退市的股票可以全部记录进去
          其次：每次更新的时候，只需要更新最近交易日数据插入到总表即可
        '''   
        
        cla_stock_mkt = pymysqlops('127.0.0.1',3306,'root','admin123','stockInfo')
        cla_stock_mkt.db_connection()
    
        # 已经存储进入mysql的最大日期
        str_sql = "select max(tradedate)FROM stockinfo.stock_mkt"
        max_date = cla_stock_mkt.select_table_by_sql(str_sql)[0][0]
        
        if datetime.strptime( max_date,'%Y-%m-%d') >= datetime.strptime( start_date,'%Y-%m-%d'):
            print("已经存储的最大日期：", max_date,
                  ",晚于起始日期:",start_date,",请重新输入开始日期")
            return
        
        if start_date > end_date:
            print("结束日期小于开始日期，请重新输入开始结束日期")        
            return  
        
        day_delta = datetime.strptime( start_date,'%Y-%m-%d') - datetime.strptime( max_date,'%Y-%m-%d')
        if day_delta !=1 :
            print("起始时间和表中最大时间不衔接，数据有遗漏，请重新输入起始日期")
            return
            
    #    str_sql = "DROP TABLE IF EXISTS stock_mkt"
    #    cla_stock_mkt.Execute_sql(str_sql)
    #    sqlcreate = """CREATE TABLE stock_mkt(
    #           TRADEDATE VARCHAR(50) NOT NULL,
    #           CODE VARCHAR(50) NOT NULL,
    #           OPEN VARCHAR(50),
    #           HIGH VARCHAR(50),
    #           LOW VARCHAR(50),
    #           CLOSE VARCHAR(50),
    #           VOLUME VARCHAR(50),
    #           AMT VARCHAR(50),
    #           VOLUME_BTIN VARCHAR(50),
    #           AMOUNT_BTIN VARCHAR(50),
    #           DEALNUM VARCHAR(50))"""
    #    cla_stock_mkt.Execute_sql(sqlcreate)
        all_tradedays = w.tdays(start_date, end_date, "TradingCalendar=SZSE").Data[0]
    
        for tradeday in all_tradedays:
            print(tradeday)
            str_date = tradeday.strftime('%Y-%m-%d')
            # 每个交易日所有股票
            str_wind = "date=" + str_date + ";sectorid=a001010100000000;field=wind_code,sec_name"
            try:
                # 如果数据为空，就报错，继续下一个日期
                all_codes = w.wset("sectorconstituent",str_wind).Data[0]
            except:
                continue
            
            # 数据为空，继续下一个（日期在中国股市建立之前，数据就为空）；
    
            str_wind = "tradeDate=" + str_date + ";priceAdj=B;cycle=D;unit=1"
            day_data = w.wss(all_codes, "open,high,low,close,volume,amt,volume_btin,amount_btin,dealnum",str_wind)
    
            # 万得 报错 ，停止运行，检查错误
            if  day_data.ErrorCode != 0:
                print("万得报错：", day_data.Data[0])
                return
            # 当日没有数据，继续下一日
            if len(day_data.Data[0]) == 0:
                continue
            
            
            in_list = []
            # 将元素便准话成为tuple类型的list
            for i in range (len(day_data.Codes)):
                tup_i=(str_date,day_data.Codes[i],)
                for j in range (len(day_data.Data)):
                    if (day_data.Data[j][i] is None):
                        tup_i = tup_i + (None,)
                    else:
                        tup_i = tup_i + (str(float('%.8f' % day_data.Data[j][i])),)
                in_list.append(tup_i)
                
            sql = "INSERT INTO stock_mkt VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
            
            cla_stock_mkt.insert_many_table(sql,in_list)
    
    
    
    def refresh_weight_zz500(self,start_date,end_date):
        '''
        start_date = "2004-12-31"
        end_date = "2020-09-29"
    
        1 深圳交易所所有交易日（2004-12-31开始）
          w.tdays("2004-12-31", "2020-09-30", "TradingCalendar=SZSE")
        2 每个交易日所有的中证500个股更新日期、代码、名称、权重、所属行业信息 
          w.wset("indexconstituent","date=2020-09-15;windcode=000905.SH")
        3 原因
          首先：最新更新日，权重，股票进出全部都能包含
          其次：每次更新的时候，只需要更新最近交易日数据插入到总表即可
        '''   
        
        cla_stock_mkt = pymysqlops('127.0.0.1',3306,'root','admin123','stockInfo')
        cla_stock_mkt.db_connection()
        
        # 已经存储进入mysql的最大日期
        str_sql = "select max(tradedate)FROM stockinfo.weight_zz500"
        max_date = cla_stock_mkt.select_table_by_sql(str_sql)[0][0]
        
        if datetime.strptime( max_date,'%Y-%m-%d') >= datetime.strptime( start_date,'%Y-%m-%d'):
            
            print("已经存储的最大日期：", max_date,
                  ",晚于起始日期:",start_date,",请重新输入开始日期")
            return
        
        if start_date > end_date:
            print("结束日期小于开始日期，请重新输入开始结束日期")        
            return
        
        day_delta = datetime.strptime( start_date,'%Y-%m-%d') - datetime.strptime( max_date,'%Y-%m-%d')
        if day_delta !=1 :
            print("起始时间和表中最大时间不衔接，数据有遗漏，请重新输入起始日期")
            return
    #    str_sql = "DROP TABLE IF EXISTS weight_zz500"
    #    cla_stock_mkt.Execute_sql(str_sql)
    #    sqlcreate = """CREATE TABLE weight_zz500(
    #           TRADEDATE VARCHAR(50) NOT NULL,
    #           CODE VARCHAR(50) NOT NULL,
    #           UP_DATE VARCHAR(50),
    #           SEC_NAME VARCHAR(50),
    #           I_WEIGHT VARCHAR(50),
    #           INDUSTRY VARCHAR(50))"""
    #    cla_stock_mkt.Execute_sql(sqlcreate)
        
        all_tradedays = w.tdays(start_date, end_date, "TradingCalendar=SZSE").Data[0]
    
        for tradeday in all_tradedays:
            print(tradeday)
            str_date = tradeday.strftime('%Y-%m-%d')
            # 每个交易日所有股票
            str_wind = "date=" + str_date + ";windcode=000905.SH" 
            
            day_data = w.wset("indexconstituent", str_wind)
            
            # 数据为空，继续下一个（日期在中证500发布之前，数据就为空）；
            
            # 万得 报错 ，停止运行，检查错误
            if  day_data.ErrorCode != 0:
                print("万得报错：", day_data.ErrorCode)
                return
            # 当日没有数据，继续下一日
            if len(day_data.Data[0]) == 0:
                continue
            
            
            in_list = []
            
            # 将元素便准话成为tuple类型的list
            for i in range (len(day_data.Codes)):
                
                tup_i=(str_date,)
                # 代码
                if (day_data.Data[1][i] is None):
                    tup_i = tup_i + (None,)
                else:
                    tup_i = tup_i + (day_data.Data[1][i],)
                
                # 更新时间
                if (day_data.Data[0][i] is None):
                    tup_i = tup_i + (None,)
                else:
                    tup_i = tup_i + (str(day_data.Data[0][i].strftime('%Y-%m-%d')),)
                # 名称
                if (day_data.Data[2][i] is None):
                    tup_i = tup_i + (None,)
                else:
                    tup_i = tup_i + (day_data.Data[2][i],)            
                # 权重
                if (day_data.Data[3][i] is None):
                    tup_i = tup_i + (None,)
                else:
                    tup_i = tup_i + (str(float('%.8f' % day_data.Data[3][i])),)            
                # 行业（？不清楚是什么行业？）
                    
                if (day_data.Data[4][i] is None):
                    tup_i = tup_i + (None,)
                else:
                    tup_i = tup_i + (day_data.Data[4][i],)
                        
                in_list.append(tup_i)
    
            sql = "INSERT INTO weight_zz500 VALUES (%s, %s, %s, %s,%s, %s)"
            cla_stock_mkt.insert_many_table(sql,in_list)
    
    
    def refresh_sw_1_indust (self,start_date,end_date):
        '''
        start_date = "1990-12-03"
        end_date = "2020-09-29"
    
        1 深圳交易所所有交易日（上海1990-12-19开始，深圳1990-12-03开始）
          w.tdays("1990-01-01", "2020-09-30", "TradingCalendar=SZSE")
        2 每个交易日所有的股票（每天所有的股票代码）
          w.wset("sectorconstituent","date=1990-12-29;sectorid=a001010100000000;field=wind_code,sec_name")
        3 所有股票的当天所属行业
          w.wss("000007.SZ,000011.SZ,000016.SZ,000012.SZ,000021.SZ,600680.SH", "industry2","industryType=1;industryStandard=1;tradeDate=19920604")
        4 这样做的原因：
          首先：退市的股票可以全部记录进去
          其次：每次更新的时候，只需要更新最近交易日数据插入到总表即可
        '''   
        
        cla_stock_mkt = pymysqlops('127.0.0.1',3306,'root','admin123','stockInfo')
        cla_stock_mkt.db_connection()
    
        # 已经存储进入mysql的最大日期
        str_sql = "select max(tradedate)FROM stockinfo.indust_sw_1"
        max_date = cla_stock_mkt.select_table_by_sql(str_sql)[0][0]
        
        if datetime.strptime( max_date,'%Y-%m-%d') >= datetime.strptime( start_date,'%Y-%m-%d'):
            
            print("已经存储的最大日期：", max_date,
                  ",晚于起始日期:",start_date,",请重新输入开始日期")
            return
        
        if start_date > end_date:
            print("结束日期小于开始日期，请重新输入开始结束日期")        
            return  
        
        day_delta = datetime.strptime( start_date,'%Y-%m-%d') - datetime.strptime( max_date,'%Y-%m-%d')
        if day_delta.days !=1 :
            print("起始时间和表中最大时间不衔接，数据有遗漏，请重新输入起始日期")
            return
    
    #    str_sql = "DROP TABLE IF EXISTS indust_sw_1"
    #    cla_stock_mkt.Execute_sql(str_sql)
    #    sqlcreate = """CREATE TABLE indust_sw_1(
    #           TRADEDATE VARCHAR(50) NOT NULL,
    #           CODE VARCHAR(50) NOT NULL,
    #           INDUSTRY2 VARCHAR(50))"""
    #    
    #    cla_stock_mkt.Execute_sql(sqlcreate)
        
        
        all_tradedays = w.tdays(start_date, end_date, "TradingCalendar=SZSE")
                # 万得 报错 ，停止运行，检查错误
        if  all_tradedays.ErrorCode != 0:
            print("万得报错：", all_tradedays.ErrorCode,all_tradedays.Data[0])
            return
        # 当日没有数据，继续下一日
        if len(all_tradedays.Data[0]) == 0:
            print('当日数据为空')
            return

        all_tradedays = all_tradedays.Data[0]
        for tradeday in all_tradedays:
            print(tradeday)
            str_date = tradeday.strftime('%Y-%m-%d')
            # 每个交易日所有股票
            str_wind = "date=" + str_date + ";sectorid=a001010100000000;field=wind_code,sec_name"
            
            all_codes = w.wset("sectorconstituent",str_wind)
            
            # 万得 报错 ，停止运行，检查错误
            if  all_codes.ErrorCode != 0:
                print("万得报错：",all_codes.ErrorCode, day_data.Data[0])
                return
            # 当日没有数据，继续下一日
            if len(all_codes.Data[0]) == 0:
                continue

            # 数据为空，继续下一个（日期在中国股市建立之前，数据就为空）；
            strdate = tradeday.strftime('%Y%m%d')
            
            str_wind = "industryType=1;industryStandard=1;tradeDate=" + strdate
            
            day_data = w.wss(all_codes.Data[0],  "industry2" , str_wind)

            # 万得 报错 ，停止运行，检查错误
            if  day_data.ErrorCode != 0:
                print("万得报错：", all_codes.ErrorCode, day_data.Data[0])
                return
            # 当日没有数据，继续下一日
            if len(day_data.Data[0]) == 0:
                continue
            
            
            in_list = []
            # 将元素标准话成为tuple类型的list
            for i in range (len(day_data.Codes)):
                tup_i=(str_date,day_data.Codes[i],)
                if (day_data.Data[0][i] is None):
                    tup_i = tup_i + (None,)
                else:
                    tup_i = tup_i + (day_data.Data[0][i],)
                in_list.append(tup_i)
                
            sql = "INSERT INTO indust_sw_1 VALUES (%s, %s, %s)"
            
            cla_stock_mkt.insert_many_table(sql,in_list)
    
